﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace Hutech.Authentication.Library.Helper
{
    [Serializable]
    public class ExcelHelper
    {
        #region Private properties

        private string connectionString = string.Empty;
        private List<String> workSheetNames;

        #endregion

        #region Public Properties

        public string ConnectionString
        {
            get { return connectionString; }
        }

        public List<String> WorkSheetNames
        {
            get { return workSheetNames; }
        }

        #endregion

        #region Methods

        /// <summary>
        /// Open Excel File is 97-2003 format
        /// </summary>
        /// <param name="fileName"></param>
        public void OpenExcelFile(string fileName, string extension)
        {
            if (extension==".xls")
                OpenExcelFile(fileName, false);
            else
                OpenExcelFile(fileName, true);
        }

        public void OpenExcelFile(string fileName, bool isOpenXMLFormat)
        {
            if (isOpenXMLFormat)
                //read a 2007, 2010 file
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                    fileName + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=YES;\"";
            else
                //read a 97-2003 file
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                    fileName + ";Extended Properties=\"Excel 8.0;IMEX=1;HDR=YES;\"";
            
            using (OleDbConnection con = new OleDbConnection(connectionString))
            {
                con.Open();
                //Lấy tất cả mọi thứ có trên các sheets
                using (System.Data.DataTable dataSet = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null))
                {
                    // Danh sách tên các Work Sheet
                    workSheetNames = new List<string>(dataSet.Rows.Count);
                    foreach (DataRow row in dataSet.Rows)
                    {
                        // Them tên các sheet vào danh sách
                        // và bỏ các ký hiệu $ ở cuối.
                        workSheetNames.Add(row["TABLE_NAME"].ToString().Trim(new[] { '$' }).Replace("$", ""));
                    }
                }
            }
        }

        public System.Data.DataTable GetWorksheet(string worksheetName)
        {
            using (OleDbConnection con = new System.Data.OleDb.OleDbConnection(connectionString))
            {
                using (OleDbDataAdapter cmd = new System.Data.OleDb.OleDbDataAdapter("select * from [" + worksheetName + "$]", con))
                {
                    con.Open();
                    using (System.Data.DataSet excelDataSet = new DataSet())
                    {
                        cmd.Fill(excelDataSet);
                        return excelDataSet.Tables[0];
                    }
                }
            }
        }

        public DataSet GetWorksheets(string worksheetName)
        {
            using (OleDbConnection con = new System.Data.OleDb.OleDbConnection(connectionString))
            {
                using (OleDbDataAdapter cmd = new System.Data.OleDb.OleDbDataAdapter("select * from [" + worksheetName + "$]", con))
                {
                    con.Open();
                    using (DataSet excelDataSet = new DataSet())
                    {
                        cmd.FillSchema(excelDataSet, SchemaType.Source);
                        cmd.Fill(excelDataSet);
                        return excelDataSet;
                    }
                }
            }
        }

        #endregion

    }
}

